
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 11/30/2010 09:48:42
-- Generated from EDMX file: C:\Users\stewarts\Documents\Visual Studio 2010\Projects\APH.Parliament2.0.Data\Branches\ParlInfoDevelopment\Source\APH.Parl2.Shared.Data\Parl2\Parl2.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [Parl2];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_ParliamentarianElectorateOffice]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ElectorateOffices] DROP CONSTRAINT [FK_ParliamentarianElectorateOffice];
GO
IF OBJECT_ID(N'[dbo].[FK_ParliamentarianPositions_Parliamentarian]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ParliamentarianPositions] DROP CONSTRAINT [FK_ParliamentarianPositions_Parliamentarian];
GO
IF OBJECT_ID(N'[dbo].[FK_ParliamentarianPositions_Position]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ParliamentarianPositions] DROP CONSTRAINT [FK_ParliamentarianPositions_Position];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Parliamentarians]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Parliamentarians];
GO
IF OBJECT_ID(N'[dbo].[Positions]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Positions];
GO
IF OBJECT_ID(N'[dbo].[ElectorateOffices]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ElectorateOffices];
GO
IF OBJECT_ID(N'[dbo].[ParliamentarianPositions]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ParliamentarianPositions];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Parliamentarians'
CREATE TABLE [dbo].[Parliamentarians] (
    [CPALId] int  NOT NULL,
    [Title] nvarchar(10)  NULL,
    [FirstName] nvarchar(50)  NULL,
    [Surname] nvarchar(50)  NULL,
    [Honorific] nvarchar(50)  NULL,
    [Electorate] nvarchar(50)  NULL,
    [State] nvarchar(4)  NULL,
    [Party] nvarchar(50)  NULL,
    [Chamber] nvarchar(25)  NULL,
    [Telephone] nvarchar(50)  NULL,
    [Fax] nvarchar(50)  NULL,
    [EmailAddress] nvarchar(100)  NULL,
    [FacebookAddress] nvarchar(100)  NULL,
    [TwitterAddress] nvarchar(100)  NULL,
    [PersonalWebsite] nvarchar(100)  NULL,
    [PartyWebsite] nvarchar(100)  NULL,
    [MPID] nvarchar(10)  NOT NULL,
    [FormerMember] bit  NOT NULL
);
GO

-- Creating table 'Positions'
CREATE TABLE [dbo].[Positions] (
    [FullTitle] nvarchar(120)  NOT NULL,
    [Id] int IDENTITY(1,1) NOT NULL
);
GO

-- Creating table 'ElectorateOffices'
CREATE TABLE [dbo].[ElectorateOffices] (
    [AddressLine1] nvarchar(100)  NULL,
    [AddressLine2] nvarchar(100)  NULL,
    [Suburb] nvarchar(50)  NULL,
    [State] nvarchar(4)  NULL,
    [PostCode] nvarchar(4)  NULL,
    [PrincipalOffice] bit  NULL,
    [Telephone] nvarchar(50)  NULL,
    [Fax] nvarchar(50)  NULL,
    [PostalAddress] nvarchar(100)  NULL,
    [PostalSuburb] nvarchar(50)  NULL,
    [PostalState] nvarchar(4)  NULL,
    [PostalPostCode] nvarchar(4)  NULL,
    [Id] int IDENTITY(1,1) NOT NULL,
    [Parliamentarian_CPALId] int  NOT NULL
);
GO

-- Creating table 'ScheduledVideos'
CREATE TABLE [dbo].[ScheduledVideos] (
    [Id] bigint IDENTITY(1,1) NOT NULL,
    [StartDateTime] nvarchar(max)  NOT NULL,
    [StartFinishTime] nvarchar(max)  NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [VideoHighLink] nvarchar(max)  NOT NULL,
    [VideoLowLink] nvarchar(max)  NOT NULL,
    [AudioLink] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Committees'
CREATE TABLE [dbo].[Committees] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [Joint] bit  NOT NULL,
    [Chamber] nvarchar(max)  NOT NULL,
    [Type] nvarchar(max)  NOT NULL,
    [Summary] nvarchar(max)  NOT NULL,
    [Current] bit  NOT NULL,
    [DateAppointed] datetime  NOT NULL,
    [DateCompleted] datetime  NOT NULL
);
GO

-- Creating table 'CommitteeArms'
CREATE TABLE [dbo].[CommitteeArms] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Type] nvarchar(max)  NOT NULL,
    [Chair_CPALId] int  NULL,
    [DeputyChair_CPALId] int  NULL,
    [Committee_Id] int  NOT NULL
);
GO

-- Creating table 'Inquiries'
CREATE TABLE [dbo].[Inquiries] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [Summary] nvarchar(max)  NOT NULL,
    [TermsOfReference] nvarchar(max)  NOT NULL,
    [Background] nvarchar(max)  NOT NULL,
    [MediaRelease] nvarchar(max)  NOT NULL,
    [Status] nvarchar(max)  NOT NULL,
    [DateReferred] datetime  NOT NULL,
    [DateSubmissionsClose] datetime  NOT NULL,
    [DateReportBy] datetime  NOT NULL,
    [CommitteeArm_Id] int  NOT NULL
);
GO

-- Creating table 'ReferredBills'
CREATE TABLE [dbo].[ReferredBills] (
    [Id] nvarchar(100)  NOT NULL
);
GO

-- Creating table 'Submissions'
CREATE TABLE [dbo].[Submissions] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Number] nvarchar(max)  NOT NULL,
    [Submitter] nvarchar(max)  NOT NULL,
    [PDFLink] nvarchar(max)  NOT NULL,
    [Inquiry_Id] int  NOT NULL
);
GO

-- Creating table 'Reports'
CREATE TABLE [dbo].[Reports] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [ParlPaperNumber] nvarchar(max)  NOT NULL,
    [DateGovtReponse] datetime  NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [DateTabled] nvarchar(max)  NOT NULL,
    [ReportHTMLLink] nvarchar(max)  NOT NULL,
    [ReportPDFLink] nvarchar(max)  NOT NULL,
    [Inquiry_Id] int  NOT NULL
);
GO

-- Creating table 'ParliamentarianPositions'
CREATE TABLE [dbo].[ParliamentarianPositions] (
    [Parliamentarian_CPALId] int  NOT NULL,
    [Positions_Id] int  NOT NULL
);
GO

-- Creating table 'CommitteeMembershipMember'
CREATE TABLE [dbo].[CommitteeMembershipMember] (
    [CommitteesAsMembers_Id] int  NOT NULL,
    [Members_CPALId] int  NOT NULL
);
GO

-- Creating table 'InquiryReferredBill'
CREATE TABLE [dbo].[InquiryReferredBill] (
    [Inquiries_Id] int  NOT NULL,
    [ReferredBills_Id] nvarchar(100)  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [CPALId] in table 'Parliamentarians'
ALTER TABLE [dbo].[Parliamentarians]
ADD CONSTRAINT [PK_Parliamentarians]
    PRIMARY KEY CLUSTERED ([CPALId] ASC);
GO

-- Creating primary key on [Id] in table 'Positions'
ALTER TABLE [dbo].[Positions]
ADD CONSTRAINT [PK_Positions]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ElectorateOffices'
ALTER TABLE [dbo].[ElectorateOffices]
ADD CONSTRAINT [PK_ElectorateOffices]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ScheduledVideos'
ALTER TABLE [dbo].[ScheduledVideos]
ADD CONSTRAINT [PK_ScheduledVideos]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Committees'
ALTER TABLE [dbo].[Committees]
ADD CONSTRAINT [PK_Committees]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'CommitteeArms'
ALTER TABLE [dbo].[CommitteeArms]
ADD CONSTRAINT [PK_CommitteeArms]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Inquiries'
ALTER TABLE [dbo].[Inquiries]
ADD CONSTRAINT [PK_Inquiries]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ReferredBills'
ALTER TABLE [dbo].[ReferredBills]
ADD CONSTRAINT [PK_ReferredBills]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Submissions'
ALTER TABLE [dbo].[Submissions]
ADD CONSTRAINT [PK_Submissions]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Reports'
ALTER TABLE [dbo].[Reports]
ADD CONSTRAINT [PK_Reports]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Parliamentarian_CPALId], [Positions_Id] in table 'ParliamentarianPositions'
ALTER TABLE [dbo].[ParliamentarianPositions]
ADD CONSTRAINT [PK_ParliamentarianPositions]
    PRIMARY KEY NONCLUSTERED ([Parliamentarian_CPALId], [Positions_Id] ASC);
GO

-- Creating primary key on [CommitteesAsMembers_Id], [Members_CPALId] in table 'CommitteeMembershipMember'
ALTER TABLE [dbo].[CommitteeMembershipMember]
ADD CONSTRAINT [PK_CommitteeMembershipMember]
    PRIMARY KEY NONCLUSTERED ([CommitteesAsMembers_Id], [Members_CPALId] ASC);
GO

-- Creating primary key on [Inquiries_Id], [ReferredBills_Id] in table 'InquiryReferredBill'
ALTER TABLE [dbo].[InquiryReferredBill]
ADD CONSTRAINT [PK_InquiryReferredBill]
    PRIMARY KEY NONCLUSTERED ([Inquiries_Id], [ReferredBills_Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [Parliamentarian_CPALId] in table 'ElectorateOffices'
ALTER TABLE [dbo].[ElectorateOffices]
ADD CONSTRAINT [FK_ParliamentarianElectorateOffice]
    FOREIGN KEY ([Parliamentarian_CPALId])
    REFERENCES [dbo].[Parliamentarians]
        ([CPALId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ParliamentarianElectorateOffice'
CREATE INDEX [IX_FK_ParliamentarianElectorateOffice]
ON [dbo].[ElectorateOffices]
    ([Parliamentarian_CPALId]);
GO

-- Creating foreign key on [Parliamentarian_CPALId] in table 'ParliamentarianPositions'
ALTER TABLE [dbo].[ParliamentarianPositions]
ADD CONSTRAINT [FK_ParliamentarianPositions_Parliamentarian]
    FOREIGN KEY ([Parliamentarian_CPALId])
    REFERENCES [dbo].[Parliamentarians]
        ([CPALId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Positions_Id] in table 'ParliamentarianPositions'
ALTER TABLE [dbo].[ParliamentarianPositions]
ADD CONSTRAINT [FK_ParliamentarianPositions_Position]
    FOREIGN KEY ([Positions_Id])
    REFERENCES [dbo].[Positions]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ParliamentarianPositions_Position'
CREATE INDEX [IX_FK_ParliamentarianPositions_Position]
ON [dbo].[ParliamentarianPositions]
    ([Positions_Id]);
GO

-- Creating foreign key on [CommitteesAsMembers_Id] in table 'CommitteeMembershipMember'
ALTER TABLE [dbo].[CommitteeMembershipMember]
ADD CONSTRAINT [FK_CommitteeMembershipMember_CommitteeMembership]
    FOREIGN KEY ([CommitteesAsMembers_Id])
    REFERENCES [dbo].[CommitteeArms]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Members_CPALId] in table 'CommitteeMembershipMember'
ALTER TABLE [dbo].[CommitteeMembershipMember]
ADD CONSTRAINT [FK_CommitteeMembershipMember_Parliamentarian]
    FOREIGN KEY ([Members_CPALId])
    REFERENCES [dbo].[Parliamentarians]
        ([CPALId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CommitteeMembershipMember_Parliamentarian'
CREATE INDEX [IX_FK_CommitteeMembershipMember_Parliamentarian]
ON [dbo].[CommitteeMembershipMember]
    ([Members_CPALId]);
GO

-- Creating foreign key on [Chair_CPALId] in table 'CommitteeArms'
ALTER TABLE [dbo].[CommitteeArms]
ADD CONSTRAINT [FK_CommitteeChair]
    FOREIGN KEY ([Chair_CPALId])
    REFERENCES [dbo].[Parliamentarians]
        ([CPALId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CommitteeChair'
CREATE INDEX [IX_FK_CommitteeChair]
ON [dbo].[CommitteeArms]
    ([Chair_CPALId]);
GO

-- Creating foreign key on [DeputyChair_CPALId] in table 'CommitteeArms'
ALTER TABLE [dbo].[CommitteeArms]
ADD CONSTRAINT [FK_CommitteeDeputyChair]
    FOREIGN KEY ([DeputyChair_CPALId])
    REFERENCES [dbo].[Parliamentarians]
        ([CPALId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CommitteeDeputyChair'
CREATE INDEX [IX_FK_CommitteeDeputyChair]
ON [dbo].[CommitteeArms]
    ([DeputyChair_CPALId]);
GO

-- Creating foreign key on [Inquiries_Id] in table 'InquiryReferredBill'
ALTER TABLE [dbo].[InquiryReferredBill]
ADD CONSTRAINT [FK_InquiryReferredBill_Inquiry]
    FOREIGN KEY ([Inquiries_Id])
    REFERENCES [dbo].[Inquiries]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [ReferredBills_Id] in table 'InquiryReferredBill'
ALTER TABLE [dbo].[InquiryReferredBill]
ADD CONSTRAINT [FK_InquiryReferredBill_ReferredBill]
    FOREIGN KEY ([ReferredBills_Id])
    REFERENCES [dbo].[ReferredBills]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_InquiryReferredBill_ReferredBill'
CREATE INDEX [IX_FK_InquiryReferredBill_ReferredBill]
ON [dbo].[InquiryReferredBill]
    ([ReferredBills_Id]);
GO

-- Creating foreign key on [Inquiry_Id] in table 'Submissions'
ALTER TABLE [dbo].[Submissions]
ADD CONSTRAINT [FK_InquirySubmission]
    FOREIGN KEY ([Inquiry_Id])
    REFERENCES [dbo].[Inquiries]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_InquirySubmission'
CREATE INDEX [IX_FK_InquirySubmission]
ON [dbo].[Submissions]
    ([Inquiry_Id]);
GO

-- Creating foreign key on [Inquiry_Id] in table 'Reports'
ALTER TABLE [dbo].[Reports]
ADD CONSTRAINT [FK_InquiryReport]
    FOREIGN KEY ([Inquiry_Id])
    REFERENCES [dbo].[Inquiries]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_InquiryReport'
CREATE INDEX [IX_FK_InquiryReport]
ON [dbo].[Reports]
    ([Inquiry_Id]);
GO

-- Creating foreign key on [Committee_Id] in table 'CommitteeArms'
ALTER TABLE [dbo].[CommitteeArms]
ADD CONSTRAINT [FK_Arm]
    FOREIGN KEY ([Committee_Id])
    REFERENCES [dbo].[Committees]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Arm'
CREATE INDEX [IX_FK_Arm]
ON [dbo].[CommitteeArms]
    ([Committee_Id]);
GO

-- Creating foreign key on [CommitteeArm_Id] in table 'Inquiries'
ALTER TABLE [dbo].[Inquiries]
ADD CONSTRAINT [FK_InquiryCommitteeArm]
    FOREIGN KEY ([CommitteeArm_Id])
    REFERENCES [dbo].[CommitteeArms]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_InquiryCommitteeArm'
CREATE INDEX [IX_FK_InquiryCommitteeArm]
ON [dbo].[Inquiries]
    ([CommitteeArm_Id]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------